﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
using Entities;

namespace Dao
{
    /// <summary>
    /// Provides a way of query with Profile from a SQL Server Database
    /// </summary>
    public class ProfileData
    {
        #region Variables private
        private IList<Profile> _listProfile = null;
        private Profile _profile = null;
        #endregion

        #region Methods public
        /// <summary>
        ///  Get list information about Profile of Members
        /// </summary>
        /// <returns>List Profile</returns>
        public IList<Profile> GetAll()
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            _listProfile = new List<Profile>();
            try
            {
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_Profile_GetAll", null);
                while (reader.Read())
                {
                    _profile = new Profile();
                    if (!reader.IsDBNull(0))
                        _profile.ProfileID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _profile.UserID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _profile.FullName = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        _profile.Professional = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _profile.Qualification = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _profile.Experience = reader.GetByte(5);
                    if (!reader.IsDBNull(6))
                        _profile.Gender = reader.GetBoolean(6);
                    if (!reader.IsDBNull(7))
                        _profile.Birthday = reader.GetDateTime(7);
                    if (!reader.IsDBNull(8))
                        _profile.AvatarPath = reader.GetString(8);
                    if (!reader.IsDBNull(9))
                        _profile.Country = reader.GetString(9);
                    if (!reader.IsDBNull(10))
                        _profile.Address = reader.GetString(10);
                    if (!reader.IsDBNull(11))
                        _profile.Phone = reader.GetString(11);
                    if (!reader.IsDBNull(12))
                        _profile.IsPublicProfile = reader.GetBoolean(12);
                    _listProfile.Add(_profile);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _listProfile;
        }
        /// <summary>
        /// Get details information profile by id of member 
        /// </summary>
        /// <param name="userId">Id of member want to view</param>
        /// <returns>Details profile of member</returns>
        public Profile GetByUserId(int userId)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@userId", SqlDbType.Int, 2) };
                parm[0].Value = userId;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.Text, "select * from Profile p join Users u on p.UserID = u.UserID where p.UserID = @userId", parm);
                while (reader.Read())
                {
                    _profile = new Profile();
                    if (!reader.IsDBNull(0))
                        _profile.ProfileID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _profile.UserID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _profile.FullName = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        _profile.Professional = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _profile.Qualification = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _profile.Experience = reader.GetByte(5);
                    if (!reader.IsDBNull(6))
                        _profile.Gender = reader.GetBoolean(6);
                    if (!reader.IsDBNull(7))
                        _profile.Birthday = reader.GetDateTime(7);
                    if (!reader.IsDBNull(8))
                        _profile.AvatarPath = reader.GetString(8);
                    if (!reader.IsDBNull(9))
                        _profile.Country = reader.GetString(9);
                    if (!reader.IsDBNull(10))
                        _profile.Address = reader.GetString(10);
                    if (!reader.IsDBNull(11))
                        _profile.Phone = reader.GetString(11);
                    if (!reader.IsDBNull(12))
                        _profile.IsPublicProfile = reader.GetBoolean(12);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _profile;
        }
        /// <summary>
        /// Insert or Update a Profile
        /// If ProfileID = 1 then insert new Profile
        /// If ProfileID = 0 then update Profile
        /// </summary>
        /// <param name="profile">Colection properties of Profile</param>
        /// <returns>True is successful , False is unsuccessful</returns>
        public bool InsertOrUpdateProfile(Profile profile)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            int result = 0;
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                    new SqlParameter("@ProfileID", SqlDbType.Int, 2),
                    new SqlParameter("@UserID", SqlDbType.Int, 2),
                    new SqlParameter("@FullName", SqlDbType.NVarChar,100),
                    new SqlParameter("@Professional", SqlDbType.NVarChar,200),
                    new SqlParameter("@Qualification", SqlDbType.NVarChar,200),
                    new SqlParameter("@Experience", SqlDbType.TinyInt),
                    new SqlParameter("@Gender", SqlDbType.Bit),
                    new SqlParameter("@Birthday", SqlDbType.DateTime),
                    new SqlParameter("@AvatarPath", SqlDbType.NVarChar,200),
                    new SqlParameter("@Country", SqlDbType.NVarChar,200),
                    new SqlParameter("@Address", SqlDbType.NVarChar,200),
                    new SqlParameter("@Phone", SqlDbType.NVarChar,100),
                    new SqlParameter("@IsPublicProfile", SqlDbType.Bit)};

                parms[0].Value = profile.ProfileID;
                parms[1].Value = profile.UserID;
                parms[2].Value = profile.FullName;
                parms[3].Value = profile.Professional;
                parms[4].Value = profile.Qualification;
                parms[5].Value = profile.Experience;
                parms[6].Value = profile.Gender;
                parms[7].Value = profile.Birthday;
                parms[8].Value = profile.AvatarPath;
                parms[9].Value = profile.Country;
                parms[10].Value = profile.Address;
                parms[11].Value = profile.Phone;
                parms[12].Value = profile.IsPublicProfile;

                if (profile.ProfileID == 1)
                    result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_Profile_Update", parms);
                else
                    result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_Profile_Insert", parms);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return result > 0 ? true : false;
        } 
        #endregion

    }
}
